#导入数据库模块
import datetime

import pymysql
import traceback
from flask import render_template
from entity import Student_exercise

class dbProcess():
    '''
    数据库管理类
    提供接口
    '''

    '''按id查询练习'''
    def query_exercises_by_id(self,table='exercise_fill',id=id):
        try:
            db = pymysql.connect(host="localhost", user="root", password="157352asd", database="ktsys", charset="utf8")
            print("数据库连接成功")
        except pymysql.Error as e:
            print("数据库连接失败：" + str(e))
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = "select * from "+table+" where id=%s"
        try:
            # 执行sql语句 -1表示无效输入
            cursor.execute(sql, [id])
            results = cursor.fetchall()
            return results
            # 提交到数据库执行
            db.commit()


        except:
            # 抛出错误信息
            traceback.print_exc()
            # 如果发生错误则回滚
            db.rollback()
            return '查询失败'
        # 关闭数据库连接
        db.close()

    '''查询所有练习'''
    def query_all_exercises(self,table='exercise_fill'):
        try:
            db = pymysql.connect(host="localhost", user="root", password="157352asd", database="ktsys", charset="utf8")
            print("数据库连接成功")
        except pymysql.Error as e:
            print("数据库连接失败：" + str(e))
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = "select * from "+table
        try:
            # 执行sql语句 -1表示无效输入
            cursor.execute(sql)
            results = cursor.fetchall()
            return results
            # 提交到数据库执行
            db.commit()


        except:
            # 抛出错误信息
            traceback.print_exc()
            # 如果发生错误则回滚
            db.rollback()
            return '查询失败'
        # 关闭数据库连接
        db.close()

    '''获取学生基本信息'''
    def get_basic_info(self,user_id):
        try:
            db = pymysql.connect(host="localhost", user="root", password="157352asd", database="ktsys", charset="utf8")
            print("数据库连接成功")
        except pymysql.Error as e:
            print("数据库连接失败：" + str(e))
        # print(user_id)
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 查询语句
        sql1 = "select count(*) from skill_builder_data_corrected_csv where user_id=%s and skill_id is not null"  # 统计做题数量
        sql2 = "select count(*) from skill_builder_data_corrected_csv where user_id=%s and correct=%s and skill_id is not null" # 统计答题正确率
        sql3 = "select count(*) from skill_builder_data_corrected_csv where user_id=%s and correct=%s and skill_id is not null"  # 统计答题正确率
        try:
            # 执行sql语句 数组传参
            cursor.execute(sql1, user_id)
            exercise_num = cursor.fetchone()[0]#学生做题数量
            cursor.execute(sql2, [user_id,1])
            exercise_correct_num = cursor.fetchone()[0]
            cursor.execute(sql3, [user_id, 0])
            exercise_incorrect_num = cursor.fetchone()[0]
        except:
            # 如果发生错误则回滚
            traceback.print_exc()
            db.rollback()
        db.close()
        return exercise_num,exercise_correct_num,exercise_incorrect_num

    '''
    功能描述：用户注册
    返回值：True表示注册成功，False表示注册失败
    '''
    def  regist_user(self,user,pwd):
        try:
            db = pymysql.connect(host="localhost", user="root", password="157352asd", database="ktsys", charset="utf8")
            print("数据库连接成功")
        except pymysql.Error as e:
            print("数据库连接失败：" + str(e))
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = "INSERT INTO student(name, pwd,grade,class,rank) VALUES (%s, %s, %s, %s, %s)"
        try:
            # 执行sql语句 -1表示无效输入
            cursor.execute(sql, [user, pwd, '-1', '-1', -1])
            # 提交到数据库执行
            db.commit()
            # 注册成功之后跳转到登录页面
            return True
        except:
            # 抛出错误信息
            traceback.print_exc()
            # 如果发生错误则回滚
            db.rollback()
            return False
        # 关闭数据库连接
        db.close()

    '''
    功能描述：按name查询一名学生
    返回值：查询成功返回id，查询失败返回-1
    '''
    def query_one_user(self,user,pwd):
        try:
            db = pymysql.connect(host="localhost", user="root", password="157352asd", database="ktsys", charset="utf8")
            print("数据库连接成功")
        except pymysql.Error as e:
            print("数据库连接失败：" + str(e))
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 查询语句
        # sql = "select * from student where name='%s' and pwd='%s';"%(user,pwd)
        sql = "select * from student where name=%s and pwd=%s"  # 通配符
        print(sql)
        try:
            # 执行sql语句 数组传参
            cursor.execute(sql, [user, pwd])
            results = cursor.fetchall()
            if len(results) == 1:
                return results
            else:
                return -1
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            traceback.print_exc()
            db.rollback()
        # 关闭数据库连接
        db.close()

    '''
    功能描述：按习题id查询答案
    返回值：查询成功返回答案，否则返回false
    '''
    def query_answer_by_id(self,table='exercise_fill',id=1):
        try:
            db = pymysql.connect(host="localhost", user="root", password="157352asd", database="ktsys", charset="utf8")
            print("数据库连接成功")
        except pymysql.Error as e:
            print("数据库连接失败：" + str(e))
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 查询语句
        # sql = "select * from student where name='%s' and pwd='%s';"%(user,pwd)
        sql = "select * from "+table+" where id=%s"  # 通配符
        print(sql)
        try:
            # 执行sql语句 数组传参
            cursor.execute(sql, [id])
            result = cursor.fetchall()
            if len(result) == 1:
                return result
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            traceback.print_exc()
            db.rollback()
        # 关闭数据库连接
        db.close()

    '''
    类型：插入语句
    功能描述：生成一条答题记录，插入到数据库
    '''
    def insert_student_exercise(self,student_exercise):
        try:
            db = pymysql.connect(host="localhost", user="root", password="157352asd", database="ktsys", charset="utf8")
            print("数据库连接成功")
        except pymysql.Error as e:
            print("数据库连接失败：" + str(e))
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 查询语句
        # sql = "select * from student where name='%s' and pwd='%s';"%(user,pwd)
        sql = "INSERT INTO student_exercise(student_id, problem_id, correct,end_time) VALUES (%s, %s, %s,%s)"  # 通配符
        print(sql)
        try:
            # 执行sql语句 数组传参
            print(student_exercise.tostring())
            # datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            cursor.execute(sql, [student_exercise.student_id,student_exercise.problem_id,student_exercise.correct,student_exercise.end_time])
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            traceback.print_exc()
            db.rollback()
        # 关闭数据库连接
        db.close()

